﻿
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Diagnostics;
using System.Xml.Serialization;
using System.IO;
using System.Globalization;

namespace BookClubIt.Data
{
    public class BciDatabase
    {
        private string m_ConnectionString;
 
        /// <summary>
        /// Creates an instance of the class using the default connection string "Default"
        /// </summary>
        public BciDatabase()
        {
            //Use the default connection string named instance
            if (ConfigurationManager.ConnectionStrings["Default"] != null)
            {
                m_ConnectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
            }
            else
            {
                throw new Exception("The Default connection string instance is missing and a name was not passed to the Bci constructor.");
            }
        }

        public BciDatabase(string instanceName)
        {
            m_ConnectionString = ConfigurationManager.ConnectionStrings[instanceName].ConnectionString;
        }

        public DataSet ExecuteDataSet(SqlCommand command)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {                
                cn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = cn;
                adapter.SelectCommand = command;
                adapter.Fill(ds);
                cn.Close();
            }
            return ds;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            return ExecuteDataSet(command);
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteDataSet(command);
        }

        public DataSet ExecuteDataSet(string procedureName)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            return ExecuteDataSet(command);
        }

        public DataSet ExecuteDataSet(string procedureName, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteDataSet(command);
        }

        public SqlDataReader ExecuteReader(SqlCommand command)
        {
            SqlConnection cn = new SqlConnection(m_ConnectionString);
            command.Connection = cn;
            cn.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }


        public SqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            return ExecuteReader(command);
        }

        public SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteReader(command);
        }

        public SqlDataReader ExecuteReader(string procedureName)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            return ExecuteReader(command);
        }

        public SqlDataReader ExecuteReader(string procedureName, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteReader(command);
        }

        public object ExecuteScalar(SqlCommand command)
        {
            object returnValue;
            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                cn.Open();
                command.Connection = cn;
                returnValue = command.ExecuteScalar();
                cn.Close();
            }
            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(string procedureName)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(string procedureName, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteScalar(command);
        }

        public int ExecuteNonQuery(SqlCommand command)
        {
            int count = 0;
            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                cn.Open();
                command.Connection = cn;
                count = command.ExecuteNonQuery();
                cn.Close();
            }
            return count;
        }

        public int ExecuteNonQueryTransactional(SqlCommand command)
        {
            int count = 0;
            SqlTransaction transaction = null;
            
            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                try
                {
                    cn.Open();
                    transaction = cn.BeginTransaction();
                    command.Transaction = transaction;
                    command.Connection = cn;
                    count = command.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    cn.Close();
                }
            }
            return count;
            
        }

        public static string SerializeToXml(object value)
        {
            StringWriter writer = new StringWriter(CultureInfo.InvariantCulture);
            XmlSerializer serializer = new XmlSerializer(value.GetType());
            serializer.Serialize(writer, value);
            return writer.ToString();
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            return ExecuteNonQuery(command);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteNonQuery(command);
        }

        public int ExecuteNonQuery(string procedureName)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            return ExecuteNonQuery(command);
        }

        public int ExecuteNonQuery(string procedureName, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteNonQuery(command);
        }

        public XmlReader ExecuteXmlReader(SqlCommand command)
        {
            SqlConnection cn = new SqlConnection(m_ConnectionString);
            command.Connection = cn;
            cn.Open();
            XmlReader reader = command.ExecuteXmlReader();
            return reader;
        }

        public XmlReader ExecuteXmlReader(CommandType commandType, string commandText)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            return ExecuteXmlReader(command);
        }

        public XmlReader ExecuteXmlReader(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(commandText);
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteXmlReader(command);
        }

        public XmlReader ExecuteXmlReader(string procedureName)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            return ExecuteXmlReader(command);
        }

        public XmlReader ExecuteXmlReader(string procedureName, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(procedureName);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return ExecuteXmlReader(command);
        }

        public static string RequestConnectionString(string connectionName)
        {
            ConnectionStringSettingsCollection connectionStringCollection = ConfigurationManager.ConnectionStrings;
            return connectionStringCollection[connectionName].ConnectionString.ToString();
        }

        public static string RequestDatabaseName(string connectionName)
        {
            string databaseName = String.Empty;
            string parameterName = "Initial Catalog=";
            string connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            int startPos = connectionString.IndexOf(parameterName);
            if (startPos > 0)
            {
                startPos += parameterName.Length;
                int endPos = connectionString.IndexOf(";", startPos);
                if (endPos > 0)
                {
                    databaseName = connectionString.Substring(startPos, endPos - startPos);
                }
            }
            return databaseName;
        }
    }
}

